package neo.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.java1234.model.PageBean;
import com.java1234.util.StringUtil;

import neo.model.Device;
import neo.util.Keyword;

@Repository("deviceDao")
public class DeviceDao {
	@Resource
	private JdbcTemplate jdbcTemplate;
	
	public void add(Device device) {
		String sql="insert into t_device values(null,?,?,?,?,?,?,?,?,?,'未知')";
		jdbcTemplate.update(sql,new Object[] {
				device.getAssetId(),
				device.getAssetName(),
				device.getType(),
				device.getUnitName(),
				device.getAssetNumber(),
				device.getInTime(),
				device.getUseDepartment(),
				device.getInstallationSite(),
				device.getKeeper()	
		});
	}
	
	public void update(Device device){
		String sql = "UPDATE `t_device` SET `assetId`=?, `assetName`=?, `type`=?, `unitName`=?, `assetNumber`=?, `inTime`=?, `useDepartment`=?, `installationSite`=?, `keeper`=? WHERE (`id`=?)";
		jdbcTemplate.update(sql,new Object[] {
				device.getAssetId(),
				device.getAssetName(),
				device.getType(),
				device.getUnitName(),
				device.getAssetNumber(),
				device.getInTime(),
				device.getUseDepartment(),
				device.getInstallationSite(),
				device.getKeeper(),
				
				device.getId()
		});
	}
	
	public List<Device> find(PageBean pageBean, Keyword keyword){
		StringBuffer sb=new StringBuffer("select * from t_device");
		
		if(keyword!=null){
			if(StringUtil.isNotEmpty(keyword.getKeyword())){
				sb.append(" where "+this.intToDBcol(keyword.getKeyname())+" like '%"+keyword.getKeyword()+"%'");
			}
		}
		
		if(pageBean!=null){
			sb.append(" limit "+pageBean.getStart()+","+pageBean.getPageSize());
		}
		
		final List<Device> deviceList=new ArrayList<Device>();
		jdbcTemplate.query(sb.toString(), new Object[]{}, new RowCallbackHandler(){

			@Override
			public void processRow(ResultSet rs) throws SQLException {
				deviceList.add(deviceMapRow(rs));
			}
			
		});
		return deviceList;
	}

	public int count(){
		StringBuffer sb=new StringBuffer("select count(*) from t_device");
		return jdbcTemplate.queryForObject(sb.toString(), Integer.class);
	}
	
	public void delete(int id) {
		String sql="delete from t_device where id=?";
		jdbcTemplate.update(sql, new Object[]{id});
	}
	
	public Device loadById(Integer id) {
		StringBuffer sb = new StringBuffer("select * from t_device where id=?");
		Device device = jdbcTemplate.queryForObject(sb.toString(),new Object[]{id},new RowMapper<Device>(){

			@Override
			public Device mapRow(ResultSet rs, int arg1) throws SQLException {
				return deviceMapRow(rs);
			}
			
		});
		return device;
	}
	
	public void delect(Integer id){
		String sql="delete from t_device where id=?";
		jdbcTemplate.update(sql, new Object[]{id});
	}
	/**
	 * 查询对象转换POJO对象
	 * @param rs ResultSet查询对象
	 * @return 目标POJO
	 * @throws SQLException
	 */
	private Device deviceMapRow(ResultSet rs)throws SQLException{
		Device dev = new Device();
		
		dev.setId(rs.getInt("id"));
		dev.setAssetId(rs.getString("assetId"));
		dev.setAssetName(rs.getString("assetName"));
		dev.setType(rs.getString("type"));
		dev.setUnitName(rs.getString("unitName"));
		dev.setAssetNumber(rs.getDouble("assetNumber"));
		dev.setInTime(rs.getString("inTime"));
		dev.setUseDepartment(rs.getString("useDepartment"));
		dev.setInstallationSite(rs.getString("installationSite"));
		dev.setKeeper(rs.getString("keeper"));
		dev.setStat(rs.getString("stat"));
		
		return dev;
	}
	
	/**
	 * keyword的数据转换列名
	 * @param keyword数字列名
	 * @return Device字符列名
	 */
	private String intToDBcol(String i){
		String col = "";
		switch(i){
		case "1":
			col = "assetId";
			break;
		case "2":
			col = "assetName";
			break;
		case "3":
			col = "type";
			break;
		case "4":
			col = "unitName";
			break;
		case "5":
			col = "assetNumber";
			break;
		case "6":
			col = "inTime";
			break;
		case "7":
			col = "useDepartment";
			break;
		case "8":
			col = "installationSite";
			break;
		case "9":
			col = "keeper";
			break;
		}
		return col;
	}
	
}
